﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using SystemDirectory.Entity;
using SystemDirectory.Common;

namespace SystemDirectory.DAO
{
    
    public class DAOService
    {
        //static Utilities.SearchType isSearch;
        //static bool isCheck;
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString2"].ToString());
        //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString2"].ToString());
        SqlCommand com;
        SqlDataAdapter adapt;
        DataTable dtResult;
        //public DataTable Get()
        //{
        //    if (con.State == ConnectionState.Closed)
        //    {
        //        con.Open();
        //    }
        //    SqlCommand com;

        //    com = new SqlCommand("SELECT * FROM Service", con);
        //    SqlDataAdapter adapt = new SqlDataAdapter(com);
        //    DataTable dtResult = new DataTable();
        //    adapt.Fill(dtResult);
        //    return dtResult;
        //}
        public DataTable Get(bool ShowInactive, Utilities.SearchType IsSearch)
        {
            //SqlCommand com;
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            //SqlCommand com;
            switch (IsSearch)
            {
                case Utilities.SearchType.TypeAll:
                    {
                        if (ShowInactive)
                            com = new SqlCommand("SELECT * FROM Service", con);
                        else
                            com = new SqlCommand("SELECT * FROM Service", con);
                        break;
                    }
                case Utilities.SearchType.Type09:
                    {
                        if (ShowInactive)
                        {
                            com = new SqlCommand("SELECT * FROM Service WHERE ServiceName LIKE '0%' OR ServiceName LIKE '1%' OR ServiceName LIKE '2%' OR ServiceName LIKE '3%' OR ServiceName LIKE '4%'OR ServiceName LIKE '5%' OR ServiceName LIKE '6%' OR ServiceName LIKE '7%' OR ServiceName LIKE '9%'", con);
                        }
                        else
                        {
                            com = new SqlCommand("SELECT * FROM Service WHERE (ServiceName LIKE '0%' OR ServiceName LIKE '1%' OR ServiceName LIKE '2%' OR ServiceName LIKE '3%' OR ServiceName LIKE '4%'OR ServiceName LIKE '5%' OR ServiceName LIKE '6%' OR ServiceName LIKE '7%' OR ServiceName LIKE '9%') AND IsActive = 'true'", con);
                        }
                        break;
                    }

                case Utilities.SearchType.TypeAE:
                    {
                        if (ShowInactive)
                        {
                            com = new SqlCommand("SELECT * FROM Service WHERE ServiceName LIKE 'A%' OR ServiceName LIKE 'B%' OR ServiceName LIKE 'C%' OR ServiceName LIKE 'D%' OR ServiceName LIKE 'E%'", con);
                        }
                        else
                        {
                            com = new SqlCommand("SELECT * FROM Service WHERE ( ServiceName LIKE 'A%' OR ServiceName LIKE 'B%' OR ServiceName LIKE 'C%' OR ServiceName LIKE 'D%' OR ServiceName LIKE 'E%') AND IsActive = 'true'", con);
                        }
                        break;
                    }
                case Utilities.SearchType.TypeFJ:
                    {
                        if (ShowInactive)
                        {
                            com = new SqlCommand("SELECT * FROM Service WHERE ServiceName LIKE 'F%' OR ServiceName LIKE 'G%' OR ServiceName LIKE 'H%' OR ServiceName LIKE 'I%' OR ServiceName LIKE 'J%'", con);
                        }
                        else
                        {
                            com = new SqlCommand("SELECT * FROM Service WHERE ( ServiceName LIKE 'F%' OR ServiceName LIKE 'G%' OR ServiceName LIKE 'H%' OR ServiceName LIKE 'I%' OR ServiceName LIKE 'J%') AND IsActive = 'true'", con);
                        }
                        break;
                    }
                case Utilities.SearchType.TypeKM:
                    if (ShowInactive)
                    {
                        com = new SqlCommand("SELECT * FROM Service WHERE ServiceName LIKE 'K%' OR ServiceName LIKE 'L%' OR ServiceName LIKE 'M%' OR ServiceName LIKE 'N%'", con);
                    }
                    else
                    {
                        com = new SqlCommand("SELECT * FROM Service WHERE (ServiceName LIKE 'K%' OR ServiceName LIKE 'L%' OR ServiceName LIKE 'M%' OR ServiceName LIKE 'N%') AND IsActive = 'true'", con);
                    }
                    break;
                case Utilities.SearchType.TypeOR:
                    if (ShowInactive)
                    {
                        com = new SqlCommand("SELECT * FROM Service WHERE ServiceName LIKE 'O%' OR ServiceName LIKE 'P%' OR ServiceName LIKE 'Q%' OR ServiceName LIKE 'R%'", con);
                    }
                    else
                    {
                        com = new SqlCommand("SELECT * FROM Service WHERE ( ServiceName LIKE 'O%' OR ServiceName LIKE 'P%' OR ServiceName LIKE 'Q%' OR ServiceName LIKE 'R%') AND IsActive = 'true'", con);
                    }
                    break;
                case Utilities.SearchType.TypeSV:
                    if (ShowInactive)
                    {
                        com = new SqlCommand("SELECT * FROM Service WHERE ServiceName LIKE 'S%' OR ServiceName LIKE 'T%' OR ServiceName LIKE 'U%' OR ServiceName LIKE 'V%'", con);
                    }
                    else
                    {
                        com = new SqlCommand("SELECT * FROM Service WHERE ( ServiceName LIKE 'S%' OR ServiceName LIKE 'T%' OR ServiceName LIKE 'U%' OR ServiceName LIKE 'V%') AND IsActive = 'true'", con);
                    }
                    break;
                case Utilities.SearchType.TypeWZ:
                    if (ShowInactive)
                    {
                        com = new SqlCommand("SELECT * FROM Service WHERE ServiceName LIKE 'W%' OR ServiceName LIKE 'X%' OR ServiceName LIKE 'Y%' OR ServiceName LIKE 'Z%'", con);
                    }
                    else
                    {
                        com = new SqlCommand("SELECT * FROM Service WHERE ( ServiceName LIKE 'W%' OR ServiceName LIKE 'X%' OR ServiceName LIKE 'Y%' OR ServiceName LIKE 'Z%') AND IsActive = 'true'", con);
                    }
                    break;

            }
            adapt = new SqlDataAdapter(com);
            dtResult = new DataTable();
            adapt.Fill(dtResult);
            return dtResult;

        }

        public bool AddService(Service sv)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand com = new SqlCommand(@"INSERT INTO Service (ContactID,ServiceName,ServiceShortDescription,SubType,LeadContact,ClientDescription,ServiceAttendance,ServiceStartExpected,ServiceStartDate,ServiceEndDate,ServiceExtendable,ServiceTimeLimited,ServiceFullDescription,DeptCode,ServiceType,ServiceDescriptionDelivery,ServiceContactCode,ServiceContactValue,ContractStagedPayment,ReferralProcessMethod)VALUES(3,@ServiceName,@ServiceShortDescription,@SubType,@LeadContact,@ClientDescription,@ServiceAttendance,@ServiceStartExpected,@ServiceStartDate,@ServiceEndDate,@ServiceExtendable,@ServiceTimeLimited,@ServiceFullDescription,@DeptCode,@ServiceType,@ServiceDescriptionDelivery,@ServiceContactCode,@ServiceContactValue,@ContractStagedPayment,@ReferralProcessMethod)", con); ;

            com.Parameters.Add("@ServiceName", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceName"].Value = sv.ServiceName;

            com.Parameters.Add("@ServiceShortDescription", SqlDbType.NVarChar, 4000);
            com.Parameters["@ServiceShortDescription"].Value = sv.serviceshortdescription;

            com.Parameters.Add("@SubType", SqlDbType.NVarChar, 255);
            com.Parameters["@SubType"].Value = sv.SubType;

            com.Parameters.Add("@ReferralProcessMethod", SqlDbType.NVarChar, 255);
            com.Parameters["@ReferralProcessMethod"].Value = sv.ReferenceProcess;

            com.Parameters.Add("@LeadContact", SqlDbType.NVarChar, 255);
            com.Parameters["@LeadContact"].Value = sv.Leadcontact;

            com.Parameters.Add("@ClientDescription", SqlDbType.NVarChar, 4000);
            com.Parameters["@ClientDescription"].Value = sv.clientdescription;

            com.Parameters.Add("@ServiceAttendance", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceAttendance"].Value = sv.ServiceAttendant;

            com.Parameters.Add("@ServiceStartExpected", SqlDbType.Date);
            com.Parameters["@ServiceStartExpected"].Value = sv.ServiceStart;

            com.Parameters.Add("@ServiceTimeLimited", SqlDbType.Date);
            com.Parameters["@ServiceTimeLimited"].Value = sv.ServiceTimeLimited;

            com.Parameters.Add("@ServiceStartDate", SqlDbType.Date);
            com.Parameters["@ServiceStartDate"].Value = sv.serviceStartDate;

            com.Parameters.Add("@ServiceEndDate", SqlDbType.Date);
            com.Parameters["@ServiceEndDate"].Value = sv.ServiceEnd;

            com.Parameters.Add("@IsActive", SqlDbType.Bit);
            com.Parameters["@IsActive"].Value = sv.ServiceActive;

            com.Parameters.Add("@ServiceExtendable", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceExtendable"].Value = sv.ServiceExtend;

            com.Parameters.Add("@DeptCode", SqlDbType.NVarChar, 255);
            com.Parameters["@DeptCode"].Value = sv.DeptCode;

            com.Parameters.Add("@ServiceType", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceType"].Value = sv.ServiceType;

            com.Parameters.Add("@ServiceDescriptionDelivery", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceDescriptionDelivery"].Value = sv.DescriptionDelivery;

            com.Parameters.Add("@ServiceFullDescription", SqlDbType.NVarChar, 4000);
            com.Parameters["@ServiceFullDescription"].Value = sv.FullDescription;

            com.Parameters.Add("@ServiceContactCode", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceContactCode"].Value = sv.ContractCode;

            com.Parameters.Add("@ServiceContactValue", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceContactValue"].Value = sv.ContractValue;

            com.Parameters.Add("@ContractStagedPayment", SqlDbType.Bit);
            com.Parameters["@ContractStagedPayment"].Value = sv.StagedPayment;

            return Convert.ToBoolean(com.ExecuteNonQuery());
        }

        public Service GetByID(int id)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand com;

            com = new SqlCommand("SELECT * FROM Service WHERE ServiceID = @id", con);
            com.Parameters.Add("@id", SqlDbType.Int);
            com.Parameters["@id"].Value = id;
            SqlDataAdapter adapt = new SqlDataAdapter(com);
            DataTable dtResult = new DataTable();
            adapt.Fill(dtResult);

            Service sv = new Service();
            sv.ServiceID = id;
            sv.ServiceName = dtResult.Rows[0]["ServiceName"].ToString();
            sv.serviceshortdescription = dtResult.Rows[0]["ServiceShortDescription"].ToString();
            sv.SubType = dtResult.Rows[0]["SubType"].ToString();
            sv.Leadcontact = dtResult.Rows[0]["LeadContact"].ToString();
            sv.clientdescription = dtResult.Rows[0]["ClientDescription"].ToString();
            sv.ServiceAttendant = dtResult.Rows[0]["ServiceAttendance"].ToString();
            sv.serviceStartDate = dtResult.Rows[0]["ServiceStartExpected"].ToString();
            sv.ServiceStart = dtResult.Rows[0]["ServiceStartDate"].ToString();
            sv.ServiceEnd = dtResult.Rows[0]["ServiceEndDate"].ToString();
            sv.ServiceExtend = dtResult.Rows[0]["ServiceExtendable"].ToString();
            sv.ServiceActive = Convert.ToBoolean(dtResult.Rows[0]["IsActive"].ToString());
            sv.FullDescription = dtResult.Rows[0]["ServiceFullDescription"].ToString();
            sv.DeptCode = dtResult.Rows[0]["DeptCode"].ToString();
            sv.ServiceType = dtResult.Rows[0]["ServiceType"].ToString();
            sv.DescriptionDelivery = dtResult.Rows[0]["ServiceDescriptionDelivery"].ToString();
            sv.ContractCode = dtResult.Rows[0]["ServiceContactCode"].ToString();
            sv.ContractValue = dtResult.Rows[0]["ServiceContactValue"].ToString();
            sv.StagedPayment = Convert.ToBoolean(dtResult.Rows[0]["ContractStagedPayment"].ToString());
            return sv;

        }

        public bool UpdateService(Service sv)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand com = new SqlCommand("UPDATE Service SET ServiceName=@ServiceName,ServiceShortDescription=ServiceShortDescription,Subtype=@SubType,LeadContact=@LeadContact,ClientDescription=@ClientDescription,ServiceAttendance=@ServiceAttendance,ServiceStartExpected=@ServiceStartExpected,ServiceStartDate=@ServiceStartDate,ServiceEndDate=@ServiceEndDate,ServiceExtendable=@ServiceExtendable,IsActive=@IsActive,ServiceFullDescription=@ServiceFullDescription,DeptCode=@DeptCode,ServiceType=@ServiceType,ServiceDescriptionDelivery=@ServiceDescriptionDelivery,ServiceContactCode=@ServiceContactCode,ServiceContactValue=@ServiceContactValue,ContactStagedPayment=@ContactStagedPayment where ServiceID = @id", con);
            com.Parameters.Add("@id", SqlDbType.Int);
            com.Parameters["@id"].Value = Convert.ToInt32(sv.ServiceID);

            com.Parameters.Add("@ServiceName", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceName"].Value = sv.ServiceName;

            com.Parameters.Add("ServiceShortDescription", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceShortDescription"].Value = sv.serviceshortdescription;

            com.Parameters.Add("SubType", SqlDbType.NVarChar, 255);
            com.Parameters["@SubType"].Value = sv.SubType;

            com.Parameters.Add("LeadContact", SqlDbType.NVarChar, 255);
            com.Parameters["@LeadContact"].Value = sv.Leadcontact;

            com.Parameters.Add("ClientDescription", SqlDbType.NVarChar, 255);
            com.Parameters["@ClientDescription"].Value = sv.clientdescription;

            com.Parameters.Add("ServiceAttendance", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceAttendance"].Value = sv.ServiceAttendant;

            com.Parameters.Add("ServiceStartExpected", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceStartExpected"].Value = sv.ServiceStart;

            com.Parameters.Add("ServiceStartDate", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceStartDate"].Value = sv.serviceStartDate;

            com.Parameters.Add("ServiceEndDate", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceEndDate"].Value = sv.ServiceEnd;

            com.Parameters.Add("IsActive", SqlDbType.Bit);
            com.Parameters["@SubType"].Value = sv.ServiceActive;

            com.Parameters.Add("ServiceExtendable", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceExtendable"].Value = sv.ServiceExtend;

            com.Parameters.Add("DeptCode", SqlDbType.NVarChar, 255);
            com.Parameters["@DeptCode"].Value = sv.DeptCode;

            com.Parameters.Add("ServiceType", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceType"].Value = sv.ServiceType;

            com.Parameters.Add("ServiceDescriptionDelivery", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceDescriptionDelivery"].Value = sv.DescriptionDelivery;

            com.Parameters.Add("ServiceContactCode", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceContactCode"].Value = sv.ContractCode;

            com.Parameters.Add("ServiceContactValue", SqlDbType.NVarChar, 255);
            com.Parameters["@ServiceContactValue"].Value = sv.ContractValue;

            com.Parameters.Add("ContactStagedPayment", SqlDbType.Bit);
            com.Parameters["@ContactStagedPayment"].Value = sv.StagedPayment;

            return Convert.ToBoolean(com.ExecuteNonQuery());

        }

    }
}



            

